/**
 *
 */
package name.yzhu.database;

import java.io.Serializable;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.tuple.Pair;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;

/**
 * @author yhzhu
 *
 */
public class DbUtil implements IDbUtil {
	private static final Log LOG = LogFactory.getLog(DbUtil.class);
	private DbSupport support = null;
	Transaction tr = null;
	boolean closed=false;

	public DbUtil(DbSupport dbSupport) {
		support = dbSupport;
	}

	@Override
	public Session getCurrentSession(){
		return support.getSession();
	}
	@SuppressWarnings("unchecked")
	public <T> List<T> queryListForHQL(String hql, int start, int pageUtil,
			String... params) throws DataBaseException {
		List<T> list = null;
		try {
			beginTrans();
			LOG.debug("query begin...");
			LOG.debug("hql: " + hql);
			Query q = null;
			q = support.getSession().createQuery(hql);
			if (params != null)
				this.setQueryForParams(q, params);
			q.setFirstResult(start);
			q.setMaxResults(pageUtil);
			list = q.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return list;
	}

	public long count(Class<?> clazz, List<Criterion> restrictions)
			throws DataBaseException {
		Session s = support.getSession();
		beginTrans();
		Criteria c = s.createCriteria(clazz);
		for (Criterion cr : restrictions) {
			c.add(cr);
		}
		c.setProjection(Projections.rowCount());
		List<?> list = null;
		try {
			list = c.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return list == null ? 0 : ((Number) list.get(0)).longValue();
	}

	public long countDistinct(Class<?> clazz, List<Criterion> restrictions,
			String propertyName) throws DataBaseException {
		Session s = support.getSession();
		beginTrans();
		Criteria c = s.createCriteria(clazz);
		for (Criterion cr : restrictions) {
			c.add(cr);
		}
		c.setProjection(Projections.countDistinct(propertyName));
		List<?> list = null;
		try {
			list = c.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return list == null ? 0 : ((Number) list.get(0)).longValue();
	}

	public Number sum(Class<?> clazz, List<Criterion> restrictions,
			String propertyName) throws DataBaseException {
		Session s = support.getSession();
		beginTrans();
		Criteria c = s.createCriteria(clazz);
		for (Criterion cr : restrictions) {
			c.add(cr);
		}
		c.setProjection(Projections.sum(propertyName));

		List<?> list = null;
		try {
			list = c.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}

		Number result = null;
		if (list != null && list.size() > 0) {
			result = (Number)list.get(0);
		}
		return result == null ? 0 : result;
	}

	public Number avg(Class<?> clazz, List<Criterion> restrictions,
			String propertyName) throws DataBaseException {
		Session s = support.getSession();
		beginTrans();
		Criteria c = s.createCriteria(clazz);
		for (Criterion cr : restrictions) {
			c.add(cr);
		}
		c.setProjection(Projections.avg(propertyName));

		List<?> list = null;
		try {
			list = c.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}

		Number result = null;
		if (list != null && list.size() > 0) {
			result = (Number)list.get(0);
		}
		return result == null ? 0 : result;
	}

	public ScrollableResults scroll(Class<?> clazz,
			List<Criterion> restrictions, int from, int count, String orderBy,
			boolean asc) throws DataBaseException {
		Session s = support.getSession();
		try {
			beginTrans();
			Criteria c = s.createCriteria(clazz);
			for (Criterion cr : restrictions) {
				c.add(cr);
			}

			if (orderBy != null) {
				c.addOrder(asc ? Order.asc(orderBy) : Order.desc(orderBy));
			}

			c.setFirstResult(from);
			c.setMaxResults(count);
			return c.scroll();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public <T> T criteriaRetrieve(Class<T> clazz, List<Criterion> restrictions) throws DataBaseException{
		Session s = support.getSession();
		try {
			beginTrans();
			Criteria c = s.createCriteria(clazz);
			for (Criterion cr : restrictions) {
				c.add(cr);
			}

			c.setFirstResult(0);
			c.setMaxResults(1);
			List<?> list = c.list();
			if (list != null && list.size() > 0)
				return (T) list.get(0);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public <T> List<T> criteriaRetrieve(Class<T> clazz,
			List<Criterion> restrictions, int from, int count,
			String orderByProperty, boolean asc) throws DataBaseException {
		Session s = support.getSession();
		try {
			beginTrans();
			Criteria c = s.createCriteria(clazz);
			for (Criterion cr : restrictions) {
				c.add(cr);
			}

			if (orderByProperty != null) {
				c.addOrder(asc ? Order.asc(orderByProperty) : Order
						.desc(orderByProperty));
			}

			c.setFirstResult(from);
			c.setMaxResults(count);
			List<T> list = c.list();
			return list;
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> criteriaRetrieve(Class<T> clazz,
			List<Criterion> restrictions, int from, int count,
			List<Pair<String, Boolean>> orderByAsc) throws DataBaseException {
		Session s = support.getSession();
		try {
			beginTrans();
			Criteria c = s.createCriteria(clazz);
			for (Criterion cr : restrictions) {
				c.add(cr);
			}

			if(orderByAsc!=null){
				for(Pair<String, Boolean> pr:orderByAsc){
					if(pr.getValue()){
						c.addOrder(Order.asc(pr.getLeft()));
					} else{
						c.addOrder(Order.desc(pr.getLeft()));
					}
				}
			}

			c.setFirstResult(from);
			c.setMaxResults(count);
			List<T> list = c.list();
			return list;
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	public <T> List<T> criteriaRetrieveAll(Class<T> clazz,
			List<Criterion> restrictions, List<Pair<String, Boolean>> orderByAsc)
			throws DataBaseException {
		boolean hasmore=true;
		int from=0;
		int count=10000;
		List<T> result=new LinkedList<T>();
		while(hasmore){
			List<T> r=criteriaRetrieve(clazz,restrictions,from,count,orderByAsc);
			result.addAll(r);
			if(r.size()<count){
				hasmore=false;
			}
		}
		return result;
	}

	public long count(Class<?> clazz, List<Criterion> restrictions,
			Map<String, String> aliasMap) throws DataBaseException {
		Session s = support.getSession();
		beginTrans();
		Criteria c = s.createCriteria(clazz);
		for (Criterion cr : restrictions) {
			c.add(cr);
		}
		if (aliasMap != null) {
			for (Map.Entry<String, String> entry : aliasMap.entrySet()) {
				c.createAlias(entry.getKey(), entry.getValue());
			}
		}
		c.setProjection(Projections.rowCount());
		List<?> list = null;
		try {

			list = c.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return list == null ? 0 : ((Number) list.get(0)).longValue();
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> criteriaRetrieve(Class<T> clazz,
			Map<String, String> aliasMap, List<Criterion> restrictions,
			int from, int count, List<Pair<String, Boolean>> orderByAsc)
			throws DataBaseException {
		Session s = support.getSession();
		try {
			beginTrans();
			Criteria c = s.createCriteria(clazz);
			for (Criterion cr : restrictions) {
				c.add(cr);
			}

			for (Map.Entry<String, String> entry : aliasMap.entrySet()) {
				c.createAlias(entry.getKey(), entry.getValue());
			}

			if(orderByAsc!=null){
				for(Pair<String, Boolean> pr:orderByAsc){
					if(pr.getValue()){
						c.addOrder(Order.asc(pr.getLeft()));
					} else{
						c.addOrder(Order.desc(pr.getLeft()));
					}
				}
			}

			c.setFirstResult(from);
			c.setMaxResults(count);
			List<T> list = c.list();
			return list;
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> projectionRetrieve(Class<T> clazz,
			List<Criterion> restrictions, ProjectionList pl, int from,
			int count, String orderByProperty, boolean asc)
			throws DataBaseException {
		Session s = support.getSession();
		try {
			beginTrans();
			Criteria c = s.createCriteria(clazz);
			for (Criterion cr : restrictions) {
				c.add(cr);
			}

			if (orderByProperty != null) {
				c.addOrder(asc ? Order.asc(orderByProperty) : Order
						.desc(orderByProperty));
			}
			if (pl != null) {
				c.setProjection(pl);
			}
			c.setFirstResult(from);
			c.setMaxResults(count);
			return c.list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	public void batchExecute(List<String> hqlList) throws DataBaseException {
		Session s = support.getSession();
		try {
			beginTrans();
			for (String hql : hqlList) {
				s.createQuery(hql).executeUpdate();
			}
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
	}

	public void saveOrUpdateOrDelForHQL(String hql, String... params)
			throws DataBaseException {
		Session session = support.getSession();
		try {
			beginTrans();
			Query q = session.createQuery(hql);
			if (params != null)
				setQueryForParams(q, params);
			q.executeUpdate();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public <T> T getObjectForFun(String fun, String column, String object,
			String conds) throws DataBaseException {
		Object o = null;
		try {
			beginTrans();
			String hql = "select " + fun + "(" + column + ") from " + object
					+ " where 1 = 1 " + conds;
			List<Object> list = support.getSession().createQuery(hql).list();
			o = list.get(0);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return (T) o;
	}

	public <T> List<T> getAll(Class<T> classs) throws DataBaseException {
		try {
			beginTrans();

			return this.getObjectForList(classs, 0, Integer.MAX_VALUE);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> getObjectForList(Class<T> classs, int start, int pageUtil)
			throws DataBaseException {
		List<T> list = null;
		try {
			beginTrans();
			list = support.getSession().createCriteria(classs)
					.setFirstResult(start).setMaxResults(pageUtil).list();
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return list;
	}

	public void update(Object obj) throws DataBaseException {
		Session session = support.getSession();

		try {
			beginTrans();
			session.update(obj);

		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
	}

	public void merge(Object obj) throws DataBaseException {
		Session session = support.getSession();

		try {
			beginTrans();
			session.merge(obj);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
	}

	public Serializable save(Object obj) throws DataBaseException {
		Session session = support.getSession();
		try {
			beginTrans();
			return session.save(obj);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return null;
	}

	/**
	 *
	 */
	public void SaveOrUpdate(Object object) throws DataBaseException {
		Session session = support.getSession();
		try {
			beginTrans();
			session.saveOrUpdate(object);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
	}

	/**
	 *
	 */
	public void Delete(Object o) throws DataBaseException {
		try {
			beginTrans();
			Session session = support.getSession();

			session.delete(o);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
	}

	@SuppressWarnings("unchecked")
	public <T> T getForObejct(Class<T> classs, Serializable id)
			throws DataBaseException {
		T o = null;
		try {
			beginTrans();
			o = (T) support.getSession().get(classs, id);
		} catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return o;
	}

	public void close() {
		if(this.closed){
			return;
		}
		try{
			commitTrans();
		}catch(DataBaseException dbe){
			LOG.warn(dbe.getMessage(),dbe);
		}catch(RuntimeException e){
			LOG.warn(e.getMessage()+" when closing a dbutil");
		}
		finally{
			closed=true;
		}

		support.closeUtil();
	}
	/**
	 * If a {@link Transaction} has been began, this operation will not have any
	 * affect.
	 */
	public void beginTrans()  throws DataBaseException {
		if(closed && tr!=null){
			tr=null;
			closed=false;
			try{
				throw new IllegalAccessError();
			}catch(IllegalAccessError iae){
				LOG.warn("This session and transaction have been closed. system will open a new one",iae);
			}
		}
		if (tr == null) {
			tr = support.getSession().beginTransaction();
			closed=false;
		}
	}

	public void commitTrans() throws DataBaseException {
		if((closed && tr!=null)
				|| (tr!=null &&
				(tr.wasRolledBack() || tr.wasCommitted())) ){
			if(closed && tr!=null){
				throw new DataBaseException("Database was closed when committing");
			}
			tr=null;
		}
		if (tr != null) {
			try {
				tr.commit();
			} catch (HibernateException e) {
				rollbackCloseSessionThenThrowoutException(e);
			} finally{
				if(tr!=null){
					tr=support.getSession().beginTransaction();
				}
			}
		}
	}

	public void flush() throws DataBaseException {
		commitTrans();
	}

	public void rollback() {
		if (tr != null) {
			try {
				tr.rollback();
			} catch (HibernateException e) {
				LOG.warn("Rollback error:" + e.getMessage(), e);
			}finally{
				support.getSession().close();
				tr = null;
			}
		}
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<?> selectNativeSQL(String sqlQuery, int start, int pageUtil, String... params)
			throws DataBaseException {
		List<Object> list = null;
		try {
			beginTrans();
			SQLQuery  q = createNativeSQL(sqlQuery, params);
			q.setFirstResult(start);
			q.setMaxResults(pageUtil);
			list=q.list();
		}catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return list;
	}
	@Override
	public int executeNativeSQL(String sql, SQLParameterBinder bind) throws DataBaseException{
		try {
			beginTrans();
			if(LOG.isDebugEnabled()){
				LOG.debug("sqlQuery: " + sql);
			}
			SQLQuery q = support.getSession().createSQLQuery(sql);
			bind.bind(q);
			return q.executeUpdate();
		} catch (DataBaseException e) {
			rollback();
			if (LOG.isDebugEnabled())
				LOG.debug(e.getMessage(), e);
			throw e;
		}
	}
	public int executeNativeSQL(String sql, String... params) throws DataBaseException{
		try {
			beginTrans();
			SQLQuery  q = createNativeSQL(sql, params);
			int ret = q.executeUpdate();
			return ret;
		} catch (DataBaseException e) {
			rollback();
			if (LOG.isDebugEnabled())
				LOG.debug(e.getMessage(), e);
			throw e;
		}
	}
	
	@Override
	public int executeNativeSQL(String sql, List<Object> param) throws DataBaseException{
		try {
			beginTrans();
			SQLQuery  q = support.getSession().createSQLQuery(sql);
			if (param.size() >0){
				for (int i=0; i<param.size(); i++){
					Object obj = param.get(i);
					if (obj.getClass() == Timestamp.class ){
						Timestamp ts = (Timestamp) obj;
						q.setDate(i, ts);
					}
					else {
						q.setParameter(i, obj);
					}
				}
			}
			LOG.debug("sqlQuery: " + sql);
			
			return q.executeUpdate();
		} catch (DataBaseException e) {
			rollback();
			if (LOG.isDebugEnabled())
				LOG.debug(e.getMessage(), e);
			throw e;
		}
	}
	
	public SQLQuery createNativeSQL(String sql, String... params) throws DataBaseException {
		SQLQuery  q = null;
		try {
			beginTrans();
			if(LOG.isDebugEnabled()){
			LOG.debug("sqlQuery: " + sql);
			}
			q = support.getSession().createSQLQuery(sql);
			if (params != null)
				setQueryForParams(q, params);
		}catch (HibernateException e) {
			rollbackCloseSessionThenThrowoutException(e);
		}
		return q;
	}


	private void rollbackCloseSessionThenThrowoutException(HibernateException e) throws DataBaseException {
		rollback();
		throw new DataBaseException("Session has been rollbacked.",e);
	}

	private void setQueryForParams(Query q, String... params) {
		for (int i = 0; i < params.length; i++) {
			q.setString(i, params[i]);
		}
	}
	@Override
	public void evict(Class<?> entityClass, Serializable id){
		try {
			support.getCach().evictEntity(entityClass, id);
			support.getCach().evictEntityRegions();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public int executeHqlSql(String hql, Object... params) throws DataBaseException{
		try {
			beginTrans();
			Query q = support.getSession().createQuery(hql);
			if (params.length >0){
				for (int i=0; i<params.length; i++){
					Object obj = params[i];
					if (obj.getClass() == Timestamp.class ){
						Timestamp ts = (Timestamp) obj;
						q.setDate(i, ts);
					}
					else {
						q.setParameter(i, obj);
					}
				}
			}
			LOG.debug("sqlQuery: " + hql);
			
			return q.executeUpdate();
		} catch (DataBaseException e) {
			rollback();
			if (LOG.isDebugEnabled())
				LOG.debug(e.getMessage(), e);
			throw e;
		}
	}
}
